﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HelloLLLLL.Blog.DAL
{
    public class DBHelper
    {
        static readonly string strConn = ConfigurationManager.AppSettings["SqlConnection"];
        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Param"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, SqlParameter[] pars)
        {
            DataSet ds = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    if (pars != null && pars.Length > 0)
                    {
                        cmd.Parameters.AddRange ( pars);
                    }
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        ds = new DataSet();
                        sda.Fill(ds);
                    }
                }
                
            }
            return ds.Tables[0];
        }

/// <summary>
///  分页
/// </summary>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
        public DataTable GetPageList(string table,string field,int start, int end)
        {
            string sql = "select * from(select *,row_number()over(order by {0} desc) as num from {1}) as t where t.num>=@start and t.num<=@end";
            sql = string.Format(sql, field, table);
            
            SqlParameter[] pars = {
                                  new SqlParameter("@start",SqlDbType.Int),
                                  new SqlParameter("@end",SqlDbType.Int)
                                  };
            pars[0].Value = start;
            pars[1].Value = end;
            DataTable da = GetDataTable(sql,pars);


            return da;

        }


        /// <summary>
        ///  带条件分页
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataTable GetPageList(string sql)
        {
            

            DataTable da = GetDataTable(sql, null);


            return da;

        }

        /// <summary>
        /// 返回一行一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Param"></param>
        /// <returns></returns>
        public object GetScalar(string sql, Dictionary<string, object> Param)
        {
            object obj = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    if (Param != null)
                    {
                        foreach (KeyValuePair<string, object> item in Param)
                        {
                            cmd.Parameters.Add(new SqlParameter(item.Key, item.Value));
                        }
                    }
                   obj= cmd.ExecuteScalar();

                }

            }
            return obj;
        }



        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Param"></param>
        /// <returns></returns>
        public int GetNonQuery(string sql)
        {
            int obj = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                   
                    obj = cmd.ExecuteNonQuery();

                }

            }
            return obj;
        }






        
    }
}
